package com.iyundao.io.mapper;

import com.iyundao.io.model.ActivityModel;
import com.iyundao.io.model.ActivityModelLookBack;
import com.iyundao.io.tools.ActitvityShow;
import com.iyundao.io.tools.ActivityModelVo;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ActivityModelCustomMapper {

    @Select("select a.id,a.activitytitle,a.organizationid,a.activitynumber," +
            "DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime," +
            "DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime,a.activityintegral," +
            "a.activitysitename,a.activityflag,ac.categoryname,ac.categoryintegral " +
            "from activitymodel a " +
            "INNER JOIN activitycategory ac " +
            "on a.activityid= ac.id " +
            "where a.organizationid in ( " +
            "SELECT convert(sid,CHAR(36)) FROM sys_dept " +
            "where sid= #{sid,jdbcType=CHAR} " +
            "or " +
            "parent_id= #{sid,jdbcType=CHAR} " +
            ") ORDER BY a.id DESC")
    List<ActivityModelVo> selectUserId(String sid);


    @Update("update activitymodel set activityflag=#{activityflag,jdbcType=INTEGER} where id=#{id,jdbcType=INTEGER}")
    int updateFlag(Integer activityflag, Integer id);

    @Select("select id from activitymodel order by id desc LIMIT 1")
    int selectLastId();

    @Update("update activitymodel set activityjoinnumber=#{activityjoinnumber,jdbcType=INTEGER} where id=#{id,jdbcType=INTEGER}")
    int updateJoinNum(Integer activityjoinnumber, Integer id);

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            "DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            "a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            "a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral" +
            " from activitymodel a " +
            "INNER JOIN activitycategory ac " +
            " on a.activityid= ac.id " +
            "where a.activitytitle like #{activitytitle,jdbcType=VARCHAR} and a.activityflag=1 " +
            "ORDER BY a.id DESC")
    List<ActivityModelVo> selectByLike(String activitytitle);

    @Select("select * from activitymodel where id=#{id,jdbcType=INTEGER}")
    ActitvityShow selectAllById(Integer id);

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            "DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            "a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            "a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral" +
            " from activitymodel a " +
            "INNER JOIN activitycategory ac " +
            " on a.activityid= ac.id " +
            "where a.id=#{id,jdbcType=INTEGER} " +
            "ORDER BY a.id DESC")
    ActivityModelVo selectShowById(Integer id);

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            "DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            "a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            "a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral" +
            " from activitymodel a " +
            "INNER JOIN activitycategory ac " +
            " on a.activityid= ac.id " +
            "where a.activityflag=1 " +
            "ORDER BY a.id DESC")
    List<ActivityModelVo> selectAll();

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            "DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            "a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            "a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral" +
            " from activitymodel a " +
            "INNER JOIN activitycategory ac " +
            " on a.activityid= ac.id " +
            "where a.activityflag=1 and a.organizationid in (SELECT sid from sys_dept where sid = #{organizationid,jdbcType=VARCHAR} or sid = (SELECT parent_id from sys_dept where sid = #{organizationid,jdbcType=VARCHAR})) " +
            "ORDER BY a.id DESC")
    List<ActivityModelVo> selectAllBy(String organizationid);

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            "DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            "a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            "a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral" +
            " from activitymodel a " +
            "INNER JOIN activitycategory ac " +
            " on a.activityid= ac.id " +
            "where a.activityflag=1 and a.organizationid in (SELECT sid from sys_dept where sid = #{organizationid,jdbcType=VARCHAR} or sid = (SELECT parent_id from sys_dept where sid = #{organizationid,jdbcType=VARCHAR})) " +
            "and a.activitytitle like #{activitytitle,jdbcType=VARCHAR} " +
            "ORDER BY a.id DESC")
    List<ActivityModelVo> selectAllByLike(String organizationid,String activitytitle);

    @Update("update activitymodel set activitytitle=#{activitytitle,jdbcType=VARCHAR}, activitycontent=#{activitycontent,jdbcType=VARCHAR}, activitystarttime=#{activitystarttime,jdbcType=TIMESTAMP}, " +
            "activityendtime=#{activityendtime,jdbcType=TIMESTAMP}, activitysignstarttime=#{activitysignstarttime,jdbcType=TIMESTAMP}, activitysignendtime=#{activitysignendtime,jdbcType=TIMESTAMP}, activitylongitude=#{activitylongitude,jdbcType=DOUBLE}, " +
            "activitylatitude=#{activitylatitude,jdbcType=DOUBLE}, activitysitename=#{activitysitename,jdbcType=VARCHAR}, activitycreatetime=#{activitycreatetime,jdbcType=TIMESTAMP}, activitynumber= #{activitynumber,jdbcType=INTEGER}, " +
            "activityjoinnumber=#{activityjoinnumber,jdbcType=INTEGER}, activityintegral=#{activityintegral,jdbcType=INTEGER}, activityimg=#{activityimg,jdbcType=VARCHAR}, userid=#{userid,jdbcType=INTEGER}, " +
            "activityflag=1, activityid=#{activityid,jdbcType=INTEGER},activitydigest=#{activitydigest,jdbcType=VARCHAR}, organizationid=#{organizationid,jdbcType=VARCHAR} " +
            "where id = #{id,jdbcType=INTEGER} ")
    int check(ActivityModel activityModel);

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            "DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            "a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            "a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral" +
            " from activitymodel a " +
            "INNER JOIN activitycategory ac " +
            " on a.activityid= ac.id " +
            "where a.organizationid in " +
            "(select sid from sys_dept where sid=#{organizationid,jdbcType=VARCHAR} or " +
            "parent_id=#{organizationid,jdbcType=VARCHAR}) " +
            "ORDER BY a.id DESC ")
    List<ActivityModelVo> selectAllByOrganizationId(String organizationid);

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            "DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            "a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            "a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral" +
            " from activitymodel a " +
            "INNER JOIN activitycategory ac " +
            " on a.activityid= ac.id " +
            "where a.activityflag=1 and a.organizationid=#{organizationid,jdbcType=VARCHAR} " +
            " ORDER BY a.id DESC ")
    List<ActivityModelVo> findByOrganizationIdAndFlag(String organizationid, Integer activityflag);

    @Select("select * from activitymodel where id=#{id,jdbcType=INTEGER}")
    ActivityModel selectById(Integer id);

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            " DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            " a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            " a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral " +
            "  from activitymodel a " +
            " INNER JOIN activitycategory ac " +
            "  on a.activityid= ac.id " +
            " where a.activitytitle like #{activitytitle,jdbcType=VARCHAR} and a.organizationid in (select sid from sys_dept where sid=#{sid,jdbcType=CHAR} or parent_id = #{sid,jdbcType=CHAR}) " +
            " ORDER BY a.id DESC ")
    List<ActivityModelVo> selectByLikeAndOrganization(String activitytitle,String sid);

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            " DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            " a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            " a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral " +
            "  from activitymodellookback a " +
            " INNER JOIN activitycategory ac " +
            "  on a.activityid= ac.id " +
            " where a.activitytitle like #{activitytitle,jdbcType=VARCHAR} and a.organizationid in (select sid from sys_dept where sid=#{sid,jdbcType=CHAR} or parent_id = #{sid,jdbcType=CHAR}) " +
            " ORDER BY a.id DESC ")
    List<ActivityModelVo> selectLookBackByLikeAndOrganization(String activitytitle,String sid);

    @Select("select a.id, a.activitytitle,a.activitycontent,DATE_FORMAT(a.activitystarttime,'%Y-%m-%d %H:%i:%s') activitystarttime,DATE_FORMAT(a.activityendtime,'%Y-%m-%d %H:%i:%s') activityendtime, " +
            " DATE_FORMAT(a.activitysignstarttime,'%Y-%m-%d %H:%i:%s') activitysignstarttime, DATE_FORMAT(a.activitysignendtime,'%Y-%m-%d %H:%i:%s') activitysignendtime, a.activitylongitude, a.activitylatitude, " +
            " a.activitysitename, DATE_FORMAT(a.activitycreatetime,'%Y-%m-%d %H:%i:%s') activitycreatetime, a.activitynumber, a.activityjoinnumber, a.activityintegral, " +
            " a.activityimg, a.userid, a.activityflag, a.activityid, a.activitydigest, a.organizationid,ac.categoryname,ac.categoryintegral,sd.real_name " +
            "  from activitymodellookback a " +
            " INNER JOIN activitycategory ac " +
            "  on a.activityid= ac.id " +
            " INNER JOIN sys_dept sd " +
            " on a.organizationid=sd.sid" +
            " where a.activitytitle like #{activitytitle,jdbcType=VARCHAR} or sd.real_name like #{activitytitle,jdbcType=VARCHAR} " +
            " ORDER BY a.id DESC ")
    List<ActivityModelVo> selectLookBackByLike(String activitytitle);

    @Delete("delete from activitymodel where id=#{id,jdbcType=INTEGER}")
    int delteActivityModel(Integer id);

    @Delete("delete from activityimage where activityid=#{id,jdbcType=INTEGER}")
    int delteActivityImage(Integer id);


}
